/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.fandi.dao.impl;

import com.fandi.config.DatabaseConnection;
import com.fandi.dao.TransactionOutDao;
import com.fandi.model.TransactionOutModel;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author fandiak
 */
public class TransactionOutDaoImpl implements TransactionOutDao {

    private final Connection conn;
    
    private final String insert     = "INSERT INTO tb_trans_out VALUES(null, ?, ?, now(), ?, now())";
    private final String update     = "UPDATE tb_trans_out SET trans_code=?, destination_name=?, trans_date=?, description=?, last_upd=now() WHERE uid=?";
    private final String delete     = "DELETE FROM tb_trans_out WHERE trans_code=?";
    private final String select     = "SELECT * FROM tb_trans_out";
    private final String selectBy   = "SELECT * FROM tb_trans_out WHERE trans_code=?";
    private final String search     = "SELECT * FROM tb_trans_out WHERE destination_name LIKE '%' ? '%' ";
    private final String count      = "SELECT COUNT(*) as totalRecord FROM tb_trans_out";

    private final String lastCode   = "SELECT MAX(uid) as maxId, trans_code FROM tb_trans_out";
    
    public TransactionOutDaoImpl() {
        conn = DatabaseConnection.getConnection();
    }
    
    @Override
    public int insert(TransactionOutModel model) throws SQLException {
        int insertedId = 0;
        try (PreparedStatement ps = conn.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);) {
            ps.setString(1, model.getTransCode());
            ps.setString(2, model.getDestination());
            ps.setString(3, model.getDescription());
            
            ps.executeUpdate();
            
            System.out.println(ps.toString());
            
            ResultSet rs = ps.getGeneratedKeys();
            
            if (rs.next()) {
                insertedId = rs.getInt(1);
            }
            
            return insertedId;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public void update(TransactionOutModel model) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public boolean delete(String code) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<TransactionOutModel> selectAll() throws SQLException {
        List<TransactionOutModel> list = new ArrayList<>();
        
        try(Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(select);) {
            
            while (rs.next()) {
                TransactionOutModel model = new TransactionOutModel();
                model.setId(rs.getInt("uid"));
                model.setTransCode(rs.getString("trans_code"));
                model.setDestination(rs.getString("destination"));
                model.setDescription(rs.getString("description"));
                model.setTransDate(rs.getDate("trans_date"));
                model.setLastUpd(rs.getDate("last_upd"));
                
                list.add(model);
            }
            
            
            return list;
        } catch (SQLException se) {
            se.printStackTrace();
            return null;
        }
    }

    @Override
    public TransactionOutModel selectBy(String code) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<TransactionOutModel> searchBy(String code) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public int count() throws SQLException {
        PreparedStatement ps = null;
        int totalRows = 0;
        
        try {
            
            ps = conn.prepareStatement(count);
            
            ResultSet rs = ps.executeQuery();
            
            if (rs.next()) {
                totalRows = rs.getInt("totalRecord");
            }
            
            return totalRows;
        } catch (SQLException se) {
            se.printStackTrace();
            return 0;
        }
    }

    @Override
    public String lastTransCode() throws SQLException {
        
        String lastTransCode = null;
        
        try (Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(lastCode)) {
            
            if(rs.next()) {
                lastTransCode = rs.getString("trans_code");
            }
            
            return lastTransCode;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    
}
